﻿
'********************************************** เริ่มเขียน 2/7/56 ******************************************

Imports MySql.Data.MySqlClient
Imports System.IO
Imports System.Data
Public Class addhistory
    Dim sql As MySqlConnection
    Dim idkey As String
    Dim ipconnect As String
    Dim usernamedb As String
    Dim dbname As String
    Dim mysqlpass As String
    Dim id_user As String
    Dim position_user As String
    Public Sub New(ByRef mysql_pass As String, ByRef ip_connect As String, ByRef user_namedb As String, ByRef db_name As String)
        InitializeComponent()
        mysqlpass = mysql_pass
        ipconnect = ip_connect
        usernamedb = user_namedb
        dbname = db_name
    End Sub
    Private Sub addhistory_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        sql = New MySqlConnection
        sql.ConnectionString = "server=" + ipconnect + ";user id=" + usernamedb + ";password=" + mysqlpass + ";database=" + dbname + ";Character Set =utf8;"
        Try
            sql.Open()
            ' MsgBox("CONNECTED TO DATABASE")
        Catch ex As Exception
            MsgBox("Can't Connect to database" + ex.Message)

            Me.Close()
        End Try

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim key As String
        Dim count As Integer

        count = 0
        key = txtsearchdp.Text

        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If

        mySqlCommand.CommandText = "SELECT * FROM department order by idDepartment;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            ListViewdp.Items.Clear()

            While (mySqlReader.Read())

                With ListViewdp.Items.Add(mySqlReader("idDepartment"))
                    .SubItems.Add(mySqlReader("departmentname"))

                End With
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()
    End Sub
    Private Sub ListViewdp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListViewdp.Click
        idkey = ListViewdp.SelectedItems(0).SubItems(0).Text

        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If

        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        mySqlCommand.CommandText = "SELECT * FROM department where idDepartment = '" & idkey & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()

        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If
        Dim count As Integer = 1

        mySqlCommand.CommandText = "SELECT DISTINCT dd_detail FROM data_device   where    idDepartment = '" & idkey & "'"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            ListViewlocat.Items.Clear()

            While (mySqlReader.Read())

                With ListViewlocat.Items.Add(count)
                    .SubItems.Add(mySqlReader("dd_detail"))

                End With
                count = count + 1
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()
    End Sub

    Private Sub ListViewlocat_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListViewlocat.Click
        idkey = ListViewlocat.SelectedItems(0).SubItems(0).Text
        'ดึงข้อมูลจากตาราง 4/7/56
        Dim dpname As String
        dpname = ListViewdp.SelectedItems(0).SubItems(1).Text
        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim count5 As String
        Dim count6 As String
        count6 = ListViewlocat.SelectedItems(0).SubItems(1).Text
        count5 = ListViewdp.SelectedItems(0).SubItems(0).Text
        mySqlCommand.CommandText = "SELECT * FROM department where idDepartment = '" & idkey & "' ;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader
            While (mySqlReader.Read())

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()

        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If
        Dim count As Integer = 1
        mySqlCommand.CommandText = "SELECT * FROM data_device where idDepartment = '" & count5 & "' and dd_detail = '" & count6 & "' "
        ' mySqlCommand.CommandText  
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader
            ListViewcom.Items.Clear()
            While (mySqlReader.Read())
                With ListViewcom.Items.Add(mySqlReader("iddata_device"))
                    If mySqlReader("type") = "Computer" Then
                        .SubItems.Add("Computer")
                    End If
                    If mySqlReader("type") = "Printer" Then
                        .SubItems.Add("Printer")
                    End If
                    If mySqlReader("type") = "Monitor" Then
                        .SubItems.Add("Monitor")
                    End If
                    If mySqlReader("type") = "Other" Then
                        .SubItems.Add("Other")
                    End If
                    If mySqlReader("type") = "Network" Then
                        .SubItems.add("Network")
                    End If
                    .SubItems.Add(mySqlReader("name"))

                    If mySqlReader("type") = "Computer" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "CPU : " + mySqlReader("c_cpu") + "mainboard : " + mySqlReader("c_mainboard") + "RAM : " + mySqlReader("c_ram") + "HARDDISK : " + mySqlReader("c_harddisk") + "VGA Card : " + mySqlReader("c_vgacard") + "Ip Number : " + mySqlReader("c_ipnumber") + "Detail : " + mySqlReader("detail") + "Serial Number : " + mySqlReader("serialnumber") + "PowerSupply : " + mySqlReader("c_ps") + "CD/DVD : " + mySqlReader("c_cd") + "Case : " + mySqlReader("c_case") + "IpAddress : " + mySqlReader("c_ipnumber") + "Computername : " + mySqlReader("c_comname") + " Windows : " + mySqlReader("c_windows") + "Office : " + mySqlReader("c_office") + "Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "Monitor" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "Size : " + mySqlReader("m_size") + "Detail : " + mySqlReader("detail") + "Serial Number : " + mySqlReader("serialnumber") + "Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "Printer" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "Type : " + mySqlReader("p_type") + "Detail : " + mySqlReader("detail") + "Serial Number : " + mySqlReader("serialnumber") + "Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "Other" Then
                        .SubItems.Add("Model : " + mySqlReader("model") + "Detail : " + mySqlReader("detail") + "Serial Number : " + mySqlReader("serialnumber") + "Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("type") = "Network" Then
                        .SubItems.Add("Serial Number : " + mySqlReader("serialnumber") + "IP Address : " + mySqlReader("c_ipnumber") + "Pass-Connect : " + mySqlReader("pass_connect") + "Pass-Config : " + mySqlReader("pass_config") + "Detail : " + mySqlReader("detail") + "Price : " + mySqlReader("price"))
                    End If
                    If mySqlReader("state_device") IsNot DBNull.Value Then
                        .SubItems.Add(mySqlReader("state_device"))
                    End If
                    'subitem แสดงข้อมูล dpname ที่เก็บมาแสดงในตาราง
                    .subitems.add(dpname)
                    .SubItems.Add(mySqlReader("dd_detail"))
                End With
                count = count + 1
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()
    End Sub

    Private Sub txtsearchdp_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtsearchdp.KeyDown
        If e.KeyCode = "13" Then
            showdatadp()
        End If
    End Sub
    Private Sub showdatadp()
        Dim mySqlCommand As New MySqlCommand
        Dim mySqlAdaptor As New MySqlDataAdapter
        Dim mySqlReader As MySqlDataReader
        Dim key As String
        Dim count As Integer

        count = 0
        key = txtsearchdp.Text

        If sql.State = ConnectionState.Closed Then
            sql.Open()
        End If

        mySqlCommand.CommandText = "SELECT * FROM department where departmentname like '%" + key + "%' order by idDepartment;"
        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = sql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            ListViewdp.Items.Clear()

            While (mySqlReader.Read())

                With ListViewdp.Items.Add(mySqlReader("idDepartment"))
                    .SubItems.Add(mySqlReader("departmentname"))

                End With
            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        sql.Close()
    End Sub

    Private Sub btnsearchdp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnsearchdp.Click
        showdatadp()
    End Sub
End Class